﻿CREATE TABLE [CD].[CashRegisterShifts] (
    [Id]             INT           NOT NULL,
    [DepartmentId]   AS            ([Id]/(100000000)),
    [CashRegisterId] INT           NOT NULL,
    [OpenUserId]     INT           NOT NULL,
    [OpenDT]         DATETIME      CONSTRAINT [DF_CashRegisterShifts_OpenDT] DEFAULT (getdate()) NOT NULL,
    [CloseUserId]    INT           NULL,
    [CloseDT]        DATETIME      NULL,
    [Note]           VARCHAR (250) NULL,
    [MD]             DATETIME      CONSTRAINT [DF__CashRegister__MD__2FEF161B] DEFAULT (getdate()) NOT NULL,
    [MW]             VARCHAR (50)  CONSTRAINT [DF__CashRegister__MW__30E33A54] DEFAULT (suser_sname()) NOT NULL,
    CONSTRAINT [PK_CashRegisterShifts] PRIMARY KEY NONCLUSTERED ([Id] ASC),
    CONSTRAINT [FK_CashRegisterShifts_CashRegisters] FOREIGN KEY ([CashRegisterId]) REFERENCES [CD].[CashRegisters] ([Id]) ON UPDATE CASCADE,
    CONSTRAINT [FK_CashRegisterShifts_CloseUser] FOREIGN KEY ([CloseUserId]) REFERENCES [CD].[DepartmentUsers] ([Id]) ON UPDATE CASCADE,
    CONSTRAINT [FK_CashRegisterShifts_OpenUser] FOREIGN KEY ([OpenUserId]) REFERENCES [CD].[DepartmentUsers] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_CashRegisterShifts_DepartmentId]
    ON [CD].[CashRegisterShifts]([DepartmentId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_CashRegisterShifts_MD]
    ON [CD].[CashRegisterShifts]([MD] ASC);


GO

CREATE TRIGGER CD.CashRegisterShiftsDeleteLog ON CD.CashRegisterShifts AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

INSERT INTO [PR].[DocDeleteLog] (TableName, IdValue, MD, MW)
SELECT 'CD.CashRegisterShifts', Id, getdate(), SYSTEM_USER FROM DELETED

END

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Подьзователь, который открыл (закрыл) смену', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'CashRegisterShifts', @level2type = N'COLUMN', @level2name = N'OpenUserId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Дата-время открытия смены', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'CashRegisterShifts', @level2type = N'COLUMN', @level2name = N'OpenDT';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Подьзователь, который закрыл смену', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'CashRegisterShifts', @level2type = N'COLUMN', @level2name = N'CloseUserId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Дата-время закрытия смены', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'CashRegisterShifts', @level2type = N'COLUMN', @level2name = N'CloseDT';

